package com.xfg.search.utils;

import com.xfg.search.entity.*;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

public class Excel {
    // 将表格中的数据添加到List集合中
    public List<User> upload(String fileName, InputStream is) throws Exception{
        Workbook workbook = getWorkbook(fileName,is);
        List<User> userList = new ArrayList<>();
        int number = workbook.getNumberOfSheets(); // 获取sheet值
        for (int i = 0; i < number; i++) {
            Sheet sheet = workbook.getSheetAt(i); // 获取表格页码
            if (sheet != null){
                int rowNum = sheet.getLastRowNum(); // 获取该页表共有多少行
                for (int j = 1; j <= rowNum; j++) {  // 一般来说第一行是标题,所以第二行开始读取
                    Row row = sheet.getRow(j); // 获取表格行
                    User user = new User();
                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    user.setUsername(row.getCell(0).getStringCellValue()); // 获取表格单元格并给User设置值
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    user.setPassword(row.getCell(1).getStringCellValue());
                    System.out.println(user);
                    userList.add(user);
                }
            }
        }
        return userList;

    }

    public List<Oil> uploadOil(String fileName, InputStream is) throws Exception{
        Workbook workbook = getWorkbook(fileName,is);
        List<Oil> oilList = new ArrayList<>();
        int number = workbook.getNumberOfSheets(); // 获取sheet值
        for (int i = 0; i < number; i++) {
            Sheet sheet = workbook.getSheetAt(i); // 获取表格页码
            if (sheet != null){
                int rowNum = sheet.getLastRowNum(); // 获取该页表共有多少行
                for (int j = 1; j <= rowNum; j++) {  // 一般来说第一行是标题,所以第二行开始读取
                    Row row = sheet.getRow(j); // 获取表格行
                    Oil oil=new Oil();
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    oil.setOilChineseName(row.getCell(1).getStringCellValue()); // 获取表格单元格并给User设置值
                    row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                    oil.setOilEnglishName(row.getCell(2).getStringCellValue());
                    row.getCell(3).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    oil.setType(row.getCell(3).getStringCellValue()); // 获取表格单元格并给User设置值
                    row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                    oil.setOrigin(row.getCell(4).getStringCellValue());
                    row.getCell(12).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    oil.setEfficacy(row.getCell(12).getStringCellValue()); // 获取表格单元格并给User设置值
                    row.getCell(13).setCellType(Cell.CELL_TYPE_STRING);
                    oil.setDocument(row.getCell(13).getStringCellValue());
                    System.out.println(oil);
                    oilList.add(oil);
                }
            }
        }
        return oilList;

    }

    public List<Oil> uploadOilPhysical(String fileName, InputStream is) throws Exception{
        Workbook workbook = getWorkbook(fileName,is);
        List<Oil> oilList = new ArrayList<>();
        int number = workbook.getNumberOfSheets(); // 获取sheet值
        for (int i = 0; i < number; i++) {
            Sheet sheet = workbook.getSheetAt(i); // 获取表格页码
            if (sheet != null){
                int rowNum = sheet.getLastRowNum(); // 获取该页表共有多少行
                for (int j = 1; j <= rowNum; j++) {  // 一般来说第一行是标题,所以第二行开始读取
                    Row row = sheet.getRow(j); // 获取表格行
                    Oil oil=new Oil();
                    row.getCell(5).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    oil.setRelativeDensity(row.getCell(5).getStringCellValue()); // 获取表格单元格并给User设置值
                    row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
                    oil.setSpecificRotation(row.getCell(6).getStringCellValue());
                    row.getCell(7).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    oil.setRefractiveIndex(row.getCell(7).getStringCellValue()); // 获取表格单元格并给User设置值
                    row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
                    oil.setMeltingPoint(row.getCell(8).getStringCellValue());
                    row.getCell(9).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    oil.setBoiling(row.getCell(9).getStringCellValue()); // 获取表格单元格并给User设置值
                    row.getCell(10).setCellType(Cell.CELL_TYPE_STRING);
                    oil.setVolatile(row.getCell(10).getStringCellValue());
                    row.getCell(11).setCellType(Cell.CELL_TYPE_STRING);
                    oil.setPdocument(row.getCell(11).getStringCellValue());
                    System.out.println(oil);
                    oilList.add(oil);
                }
            }
        }
        return oilList;

    }

    public List<Oil> uploadOilToxic(String fileName, InputStream is) throws Exception{
        Workbook workbook = getWorkbook(fileName,is);
        List<Oil> oilList = new ArrayList<>();
        int number = workbook.getNumberOfSheets(); // 获取sheet值
        for (int i = 0; i < number; i++) {
            Sheet sheet = workbook.getSheetAt(i); // 获取表格页码
            if (sheet != null){
                int rowNum = sheet.getLastRowNum(); // 获取该页表共有多少行
                for (int j = 1; j <= rowNum; j++) {  // 一般来说第一行是标题,所以第二行开始读取
                    Row row = sheet.getRow(j); // 获取表格行
                    Oil oil=new Oil();
                    row.getCell(14).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    oil.setToxic(row.getCell(14).getStringCellValue()); // 获取表格单元格并给User设置值
                    row.getCell(15).setCellType(Cell.CELL_TYPE_STRING);
                    oil.setTdocument(row.getCell(15).getStringCellValue());
                    System.out.println(oil);
                    oilList.add(oil);
                }
            }
        }
        return oilList;

    }

    public List<Oil> uploadOilComposition(String fileName, InputStream is) throws Exception{
        Workbook workbook = getWorkbook(fileName,is);
        List<Oil> oilList = new ArrayList<>();
        int number = workbook.getNumberOfSheets(); // 获取sheet值
        for (int i = 0; i < number; i++) {
            Sheet sheet = workbook.getSheetAt(i); // 获取表格页码
            if (sheet != null){
                int rowNum = sheet.getLastRowNum(); // 获取该页表共有多少行
                for (int j = 1; j <= rowNum; j++) {  // 一般来说第一行是标题,所以第二行开始读取
                    Row row = sheet.getRow(j); // 获取表格行
                    Oil oil=new Oil();
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    oil.setOilChineseName(row.getCell(1).getStringCellValue()); // 获取表格单元格并给User设置值
                    row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                    oil.setComposition(row.getCell(2).getStringCellValue());
                    row.getCell(3).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    oil.setExtractionMethod(row.getCell(3).getStringCellValue()); // 获取表格单元格并给User设置值
                    row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                    oil.setDocument(row.getCell(4).getStringCellValue());
                    System.out.println(oil);
                    oilList.add(oil);
                }
            }
        }
        return oilList;

    }

    public List<Oil> uploadOilApplication(String fileName, InputStream is) throws Exception{
        Workbook workbook = getWorkbook(fileName,is);
        List<Oil> oilList = new ArrayList<>();
        int number = workbook.getNumberOfSheets(); // 获取sheet值
        for (int i = 0; i < number; i++) {
            Sheet sheet = workbook.getSheetAt(i); // 获取表格页码
            if (sheet != null){
                int rowNum = sheet.getLastRowNum(); // 获取该页表共有多少行
                for (int j = 1; j <= rowNum; j++) {  // 一般来说第一行是标题,所以第二行开始读取
                    Row row = sheet.getRow(j); // 获取表格行
                    Oil oil=new Oil();
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    oil.setOilChineseName(row.getCell(1).getStringCellValue()); // 获取表格单元格并给User设置值
                    row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                    oil.setDrugPromotedCName(row.getCell(2).getStringCellValue());
                    row.getCell(3).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    oil.setDrugPromotedEName(row.getCell(3).getStringCellValue()); // 获取表格单元格并给User设置值
                    row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                    oil.setMassFraction(row.getCell(4).getStringCellValue());
                    row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
                    oil.setDocument(row.getCell(5).getStringCellValue());
                    System.out.println(oil);
                    oilList.add(oil);
                }
            }
        }
        return oilList;

    }

    public List<ChineseDrug> uploadDrug(String fileName, InputStream is) throws Exception{
        Workbook workbook = getWorkbook(fileName,is);
        List<ChineseDrug> chineseDrugList = new ArrayList<>();
        int number = workbook.getNumberOfSheets(); // 获取sheet值
        for (int i = 0; i < number; i++) {
            Sheet sheet = workbook.getSheetAt(i); // 获取表格页码
            if (sheet != null){
                int rowNum = sheet.getLastRowNum(); // 获取该页表共有多少行
                for (int j = 1; j <= rowNum; j++) {  // 一般来说第一行是标题,所以第二行开始读取
                    Row row = sheet.getRow(j); // 获取表格行
                    ChineseDrug chineseDrug=new ChineseDrug();
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    chineseDrug.setDrugChineseName(row.getCell(1).getStringCellValue()); // 获取表格单元格并给User设置值
                    row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                    chineseDrug.setDrugEnglishName(row.getCell(2).getStringCellValue());
                    row.getCell(3).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    chineseDrug.setLatinName(row.getCell(3).getStringCellValue()); // 获取表格单元格并给User设置值
                    row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                    chineseDrug.setDiffName(row.getCell(4).getStringCellValue());
                    row.getCell(5).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    chineseDrug.setPart(row.getCell(5).getStringCellValue()); // 获取表格单元格并给User设置值
                    row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
                    chineseDrug.setClassify(row.getCell(6).getStringCellValue());
                    row.getCell(7).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    chineseDrug.setTaste(row.getCell(7).getStringCellValue()); // 获取表格单元格并给User设置值
                    row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
                    chineseDrug.setCategory(row.getCell(8).getStringCellValue());
                    row.getCell(9).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    chineseDrug.setEfficacy(row.getCell(9).getStringCellValue()); // 获取表格单元格并给User设置值
                    row.getCell(10).setCellType(Cell.CELL_TYPE_STRING);
                    chineseDrug.setAttending(row.getCell(10).getStringCellValue());
                    System.out.println(chineseDrug);
                    chineseDrugList.add(chineseDrug);
                }
            }
        }
        return chineseDrugList;

    }

    public List<DrugPromoted> uploadDrugPromotedStruct(String fileName, InputStream is) throws Exception{
        Workbook workbook = getWorkbook(fileName,is);
        List<DrugPromoted> drugPromotedList = new ArrayList<>();
        int number = workbook.getNumberOfSheets(); // 获取sheet值
        for (int i = 0; i < number; i++) {
            Sheet sheet = workbook.getSheetAt(i); // 获取表格页码
            if (sheet != null){
                int rowNum = sheet.getLastRowNum(); // 获取该页表共有多少行
                for (int j = 1; j <= rowNum; j++) {  // 一般来说第一行是标题,所以第二行开始读取
                    Row row = sheet.getRow(j); // 获取表格行

                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(3).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(5).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(7).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(9).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    row.getCell(10).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(11).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    row.getCell(12).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(13).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    row.getCell(14).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(15).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    row.getCell(16).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(17).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    row.getCell(18).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(19).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
//                    DrugPromoted drugPromoted=new DrugPromoted("",row.getCell(1).getStringCellValue(),row.getCell(2).getStringCellValue(),row.getCell(3).getStringCellValue(),row.getCell(4).getStringCellValue(),row.getCell(5).getStringCellValue(),row.getCell(6).getStringCellValue(),row.getCell(7).getStringCellValue(),row.getCell(8).getStringCellValue(),row.getCell(0).getStringCellValue(),row.getCell(10).getStringCellValue(),row.getCell(11).getStringCellValue(),row.getCell(12).getStringCellValue(),row.getCell(13).getStringCellValue(),row.getCell(14).getStringCellValue(),row.getCell(15).getStringCellValue(),row.getCell(16).getStringCellValue(),row.getCell(17).getStringCellValue(),row.getCell(18).getStringCellValue(),row.getCell(19).getStringCellValue());
//                    System.out.println(drugPromoted);
//                    drugPromotedList.add(drugPromoted);
                }
            }
        }
        return drugPromotedList;

    }

    public List<Composition> uploadCompositionStruct(String fileName, InputStream is) throws Exception{
        Workbook workbook = getWorkbook(fileName,is);
        List<Composition> compositionList = new ArrayList<>();
        int number = workbook.getNumberOfSheets(); // 获取sheet值
        for (int i = 0; i < number; i++) {
            Sheet sheet = workbook.getSheetAt(i); // 获取表格页码
            if (sheet != null){
                int rowNum = sheet.getLastRowNum(); // 获取该页表共有多少行
                for (int j = 1; j <= rowNum; j++) {  // 一般来说第一行是标题,所以第二行开始读取
                    Row row = sheet.getRow(j); // 获取表格行
                    row.getCell(0).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(3).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(5).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(7).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(9).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    row.getCell(10).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(11).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    row.getCell(12).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(13).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    row.getCell(14).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(15).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    row.getCell(16).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(17).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
                    row.getCell(18).setCellType(Cell.CELL_TYPE_STRING);
                    row.getCell(19).setCellType(Cell.CELL_TYPE_STRING); // 将该单元格获取出来的值设为String类型
//                    Composition composition=new Composition("",row.getCell(1).getStringCellValue(),row.getCell(2).getStringCellValue(),row.getCell(3).getStringCellValue(),row.getCell(4).getStringCellValue(),row.getCell(5).getStringCellValue(),row.getCell(6).getStringCellValue(),row.getCell(7).getStringCellValue(),row.getCell(8).getStringCellValue(),row.getCell(0).getStringCellValue(),row.getCell(10).getStringCellValue(),row.getCell(11).getStringCellValue(),row.getCell(12).getStringCellValue(),row.getCell(13).getStringCellValue(),row.getCell(14).getStringCellValue(),row.getCell(15).getStringCellValue(),row.getCell(16).getStringCellValue(),row.getCell(17).getStringCellValue(),row.getCell(18).getStringCellValue(),row.getCell(19).getStringCellValue());
//                    System.out.println(composition);
//                    compositionList.add(composition);
                }
            }
        }
        return compositionList;

    }


    // 判断传入的文件是哪种类型的excel文件
    public Workbook getWorkbook(String fileName, InputStream is) throws Exception{
        Workbook workbook = null;
        String name = fileName.substring(fileName.lastIndexOf("."));
        System.out.println(name);
        if (".xls".equals(name)){
            workbook = new HSSFWorkbook(is);
        }else if (".xlsx".equals(name)){
            workbook = new XSSFWorkbook(is);
        }else {
            throw new Exception(" 请上传.xls/.xlsx格式文件！");
        }
        return workbook;
    }
}
